package com.sprouting.metadata.core.service;

import com.sprouting.metadata.core.enums.LetterCaseEnum;
import com.sprouting.metadata.core.interfaces.MetadataQueryInterfaces;
import com.sprouting.metadata.core.model.*;
import com.sprouting.metadata.core.service.queryImpl.DefaultQueryService;
import com.sprouting.metadata.core.service.queryImpl.OracleInspector;
import com.sprouting.metadata.core.service.queryImpl.PGInspector;
import com.sprouting.metadata.core.service.queryImpl.SqlServer2005Inspector;
import com.sprouting.metadata.core.utils.JdbcTool;
import com.sprouting.metadata.core.utils.StringTool;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 表查询相关的处理
 *
 * @author ：LX（长沙麓谷）
 * @date 创建时间： 2022/4/24 13:52
 */
public class MetadataQueryHandle {

    /**
     * 核心处理过程
     * @param simpleDataSource 数据源配置
     * @param metadataConfig 配置信息
     * @throws SQLException
     * @return 查询的表结构信息
     */
    public List<TableModel> coreHandle(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig) throws SQLException {
        // 配置文件处理
        metadataConfig = getDatabaseConfig(simpleDataSource, metadataConfig);
        // 自动处理数据库大小写
        LetterCaseEnum letterCaseEnum = JdbcTool.initLetterCase(simpleDataSource);
        simpleDataSource.setLetterCaseEnum(letterCaseEnum);

        // 1、查询之前
        if (metadataConfig.getMetadataQueryProcessInterfaces() != null) {
            metadataConfig.getMetadataQueryProcessInterfaces().start();
        }

        // 获取表信息
        List<TableModel> tableModelList = null;

        try {
            // 获取字段和表
            tableModelList = getColumns(simpleDataSource, metadataConfig);

            if (tableModelList.isEmpty()){
                tableModelList = new ArrayList<>();

            } else {
                // 选择处理的实现
                MetadataQueryInterfaces metadataQueryInterfaces = selectImpl(simpleDataSource);
                // 处理具体的数据
                detailedDataSupplement(simpleDataSource, metadataConfig, metadataQueryInterfaces, tableModelList);
            }

        } finally {
            // 第三方接口，最后结束调用的接口
            if (metadataConfig.getMetadataQueryProcessInterfaces() != null){
                metadataConfig.getMetadataQueryProcessInterfaces().processComplete(tableModelList);
            }
        }

        return tableModelList;
    }

    /**
     * 获取数据库基础信息
     * @param simpleDataSource 数据源
     * @return
     * @throws SQLException
     */
    public DatabaseInfoBase getDatabaseInfoBase(SimpleDataSource simpleDataSource) throws SQLException {
        Connection connection = simpleDataSource.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();

        DatabaseInfoBase databaseInfoBase = new DatabaseInfoBase();
        databaseInfoBase.setUserName(metaData.getUserName());
        databaseInfoBase.setUrl(metaData.getURL());
        databaseInfoBase.setReadOnly(metaData.isReadOnly());
        databaseInfoBase.setDatabaseProductName(metaData.getDatabaseProductName());
        databaseInfoBase.setDatabaseProductVersion(metaData.getDatabaseProductVersion());
        databaseInfoBase.setDriverName(metaData.getDriverName());
        databaseInfoBase.setDriverVersion(metaData.getDriverVersion());

        return databaseInfoBase;
    }

    /**
     * 详细数据补充
     * @param simpleDataSource 数据源配置
     * @param metadataConfig 配置信息
     * @param metadataQueryInterfaces 实际执行查询的方法，注意，当前类是接口
     * @param tableModelList 已查询出的表信息
     */
    private void detailedDataSupplement(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig, MetadataQueryInterfaces metadataQueryInterfaces, List<TableModel> tableModelList) throws SQLException {
        // 更详细的表 - 注释 信息
        Map<String, String> tableMap = metadataQueryInterfaces.getTable(simpleDataSource, metadataConfig);
        // 更详细的 字段- 注释 信息
        Map<String, Map<String, String>> columnMap = metadataQueryInterfaces.getColumn(simpleDataSource, metadataConfig);

        JdbcTypeInformationService jdbcTypeInformationService = new JdbcTypeInformationService();

        for (TableModel tableModel : tableModelList){
            // 填充表的注释
            if (tableMap != null && tableMap.containsKey(tableModel.getTableName())){
                tableModel.setRemarks(tableMap.get(tableModel.getTableName()));
            }

            // 字段注释, 字段 - 注释
            Map<String, String> columnCommentsMap = null;
            if (columnMap != null && columnMap.containsKey(tableModel.getTableName())) {
                columnCommentsMap = columnMap.get(tableModel.getTableName());
            }

            // 处理字段
            List<ColumnModel> columnModelList = tableModel.getColumnModelList();
            for (ColumnModel columnModel : columnModelList){
                // 获取java类型
                FullyQualifiedJavaType fullyQualifiedJavaType = jdbcTypeInformationService.calculateJavaType(columnModel, metadataConfig);
                if (fullyQualifiedJavaType != null) {
                    columnModel.setFullyQualifiedJavaType(fullyQualifiedJavaType);
                    columnModel.setJdbcTypeName(jdbcTypeInformationService.calculateJdbcTypeName(columnModel));
                }

                // 驼峰处理
                if (metadataConfig.getUseCamelCase()){
                    columnModel.setJavaProperty(StringTool.toCamelCaseString(columnModel.getColumnName(), false));
                } else {
                    columnModel.setJavaProperty(StringTool.getValidPropertyName(columnModel.getColumnName()));
                }

                // 增加注释
                if (columnCommentsMap != null && columnCommentsMap.containsKey(columnModel.getColumnName())) {
                    columnModel.setRemarks(columnCommentsMap.get(columnModel.getColumnName()));
                }
            }

            // 主键补充
            calculatePrimaryKey(simpleDataSource, metadataConfig, tableModel);
        }
    }

    /**
     * 获取主键
     * @param metadataConfig 配置信息
     * @param tableModel 表信息
     */
    private void calculatePrimaryKey(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig, TableModel tableModel) {
        ResultSet rs = null;
        try {
            Connection connection = simpleDataSource.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            rs = metaData.getPrimaryKeys(metadataConfig.getCatalog(), metadataConfig.getSchemaPattern(), tableModel.getTableName());

        } catch (SQLException e) {
            JdbcTool.closeResultSet(rs);
            return;
        }

        try {
            // Map<Short, String> keyColumns = new TreeMap<>();
            while (rs.next()) {
                //主键列名
                String columnName = rs.getString("COLUMN_NAME");
                //主键顺序
                short keySeq = rs.getShort("KEY_SEQ");
                // keyColumns.put(keySeq, columnName);

                // 添加主键标记
                List<ColumnModel> columnModelList = tableModel.getColumnModelList();
                for (ColumnModel columnModel : columnModelList){
                    if (columnModel.getColumnName().equalsIgnoreCase(columnName)){
                        columnModel.setPk(true);
                    }
                }
            }

        } catch (SQLException e) {
        } finally {
            JdbcTool.closeResultSet(rs);
        }
    }

    /**
     * 选择获取元数据的实现方式
     * @param simpleDataSource 数据源配置
     * @return 实现的方式
     */
    private MetadataQueryInterfaces selectImpl(SimpleDataSource simpleDataSource){
        if (simpleDataSource == null) {
            throw new NullPointerException("数据源不能为空(Argument dataSource can't be null)!");
        }

        switch (simpleDataSource.getDatabaseEnum()) {
            case ORACLE:
                return new OracleInspector();
            case POSTGRESQL:
                return new PGInspector();
            case SQLSERVER2005:
                return new SqlServer2005Inspector();
            case DB2:
            case HSQLDB:
            case MARIADB:
            case MYSQL:
            case MYSQL5:
            default:
                return new DefaultQueryService();
        }
    }


    /**
     * 获取全部的表和字段
     * 该方式可以获取到表和字段，但是并不一定能获取到注释等信息，离完整的数据结构是有差距的，这里只是初步的获取部分信息
     * @param simpleDataSource 数据源配置
     * @param metadataConfig 配置信息
     */
    private List<TableModel> getColumns(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig) throws SQLException {
        // 表名-表信息
        Map<String, TableModel> tableMap = new HashMap<>();

        Connection connection = simpleDataSource.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();

        ResultSet rs = metaData.getColumns(metadataConfig.getCatalog(), metadataConfig.getSchemaPattern(), metadataConfig.getTableNamePattern(), null);
        while (rs.next()) {
            ColumnModel column = new ColumnModel();
            column.setJdbcType(rs.getInt("DATA_TYPE"));
            column.setType(rs.getString("TYPE_NAME"));
            column.setLength(rs.getInt("COLUMN_SIZE"));
            column.setColumnName(rs.getString("COLUMN_NAME"));
            // 允许为空：0 不允许 1 允许
            column.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
            column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
            column.setRemarks(rs.getString("REMARKS"));
            column.setDefaultValue(rs.getString("COLUMN_DEF"));
            column.setPk(false);

            TableModel table = new TableModel();
            table.setCatalog(rs.getString("TABLE_CAT"));
            table.setSchema(rs.getString("TABLE_SCHEM"));
            table.setTableName(rs.getString("TABLE_NAME"));
            // table.setTableType(rs.getString("TABLE_TYPE"));
            table.setRemarks(rs.getString("REMARKS"));

            if (tableMap.containsKey(table.getTableName())){
                // 存在表信息
                TableModel tableModel = tableMap.get(table.getTableName());
                List<ColumnModel> columnModelList = tableModel.getColumnModelList();
                columnModelList.add(column);
                // 第三方接口
                if (metadataConfig.getMetadataQueryProcessInterfaces() != null){
                    metadataConfig.getMetadataQueryProcessInterfaces().processColumn(table, column);
                }

            } else {
                // 不存在
                table.setColumnModelList(new ArrayList<>());
                tableMap.put(table.getTableName(), table);
                // 第三方接口
                if (metadataConfig.getMetadataQueryProcessInterfaces() != null){
                    metadataConfig.getMetadataQueryProcessInterfaces().processTable(table);
                }
            }
        }

        JdbcTool.closeResultSet(rs);

        // 转换结构
        List<TableModel> resultList = new ArrayList<>();
        for (Map.Entry<String, TableModel> entry : tableMap.entrySet()) {
            TableModel tab = entry.getValue();
            resultList.add(tab);
        }

        return resultList;
    }

    /**
     * 根据数据库转换配置，将数据补全
     * 从元数据中读取配置，修改获取的数据大小写
     * @param metadataConfig 配置信息
     * @throws SQLException
     */
    private MetadataConfig getDatabaseConfig(SimpleDataSource simpleDataSource, MetadataConfig metadataConfig) throws SQLException {
        if (metadataConfig == null){
            metadataConfig = new MetadataConfig();
        }

        Connection connection = simpleDataSource.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();

        String catalog;
        String schemaPattern;
        String tableNamePattern;
        if (metaData.storesLowerCaseIdentifiers()) {
            catalog = metadataConfig.getCatalog() == null ? null : metadataConfig.getCatalog().toLowerCase();
            schemaPattern = metadataConfig.getSchemaPattern() == null ? null : metadataConfig.getSchemaPattern().toLowerCase();
            tableNamePattern = metadataConfig.getTableNamePattern() == null ? null : metadataConfig.getTableNamePattern().toLowerCase();

        } else if (metaData.storesUpperCaseIdentifiers()) {
            catalog = metadataConfig.getCatalog() == null ? null : metadataConfig.getCatalog().toUpperCase();
            schemaPattern = metadataConfig.getSchemaPattern() == null ? null : metadataConfig.getSchemaPattern().toUpperCase();
            tableNamePattern = metadataConfig.getTableNamePattern() == null ? null : metadataConfig.getTableNamePattern().toUpperCase();

        } else {
            catalog = metadataConfig.getCatalog();
            schemaPattern = metadataConfig.getSchemaPattern();
            tableNamePattern = metadataConfig.getTableNamePattern();
        }

        metadataConfig.setCatalog(catalog);
        metadataConfig.setSchemaPattern(schemaPattern);
        metadataConfig.setTableNamePattern(tableNamePattern);

        return metadataConfig;
    }


}
